package database;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;

import ref.Beijing;
import ref.Center_hospital;
import ref.GuangZhouJunQu;
import ref.Hubei;
import ref.Qproduct;
import ref.Tradeinfo;
import ref.YNF;

import util.XxlsAbstract;

public class XlsxBig extends XxlsAbstract {
	private static Logger log = Logger.getLogger("XxlsBig");
	private Connection conn = null;
	private Statement statement = null;
	private PreparedStatement newStatement = null;
	private String tableName = "q201301_03gzjq_x04all";//"q201201_12_new_all";//database table name
	private static String folderPath = "I:/data/黄总/2013年BJQ1/";
	private static String fileName = "bj1301_01-13";

	public static void main(String[] args) throws Exception {
		long time = System.currentTimeMillis();
		XlsxBig howto = new XlsxBig();
		log.info("begin to connect database");
		howto.processOneSheet(folderPath+fileName+".xlsx",1);//default sheet is first sheet
		howto.close();
		log.info("end in "+ (System.currentTimeMillis() - time)/1000 + " seconds!");
	}
	
	public XlsxBig() throws SQLException{
		this.conn = getNew_Conn();
		this.statement = conn.createStatement();
	}
	
	public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
		try {
			if (curRow == 0 && sheetIndex == 0 ) {  
	            StringBuffer preSql = new StringBuffer("insert into " + tableName  
	                    + " values(");
	            int c = rowlist.size();
	            for (int i = 0; i < c; i++) {  
	                preSql.append("?,");
	            }
	            preSql.deleteCharAt(preSql.length() - 1);
	            preSql.append(")");
	            conn.setAutoCommit(false);
	            newStatement = conn.prepareStatement(preSql.toString());  
	  
	        }else if(curRow > 0) {
	        	new Beijing().dataType(newStatement, rowlist);//Qproduct()//the data object 
	            newStatement.addBatch();
	            if (curRow % 1000 == 0) {  
	                newStatement.executeBatch();  
	                conn.commit();  
	            }
	        } 
		} catch (Exception e) {
			e.printStackTrace(System.err);
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace(System.err);
			}
		}
    }
	
    private static Connection getNew_Conn() {
        Connection conn = null;
        Properties props = new Properties();
        FileInputStream fis = null;

        try {
            fis = new FileInputStream(new File("src/conf/database.properties").getAbsolutePath());//Relative path of properties file
            props.load(fis);
            Class.forName("oracle.jdbc.OracleDriver");
            StringBuffer jdbcURLString = new StringBuffer();
            jdbcURLString.append("jdbc:oracle:thin:@");
            jdbcURLString.append(props.getProperty("host"));
            jdbcURLString.append(":");
            jdbcURLString.append(props.getProperty("port"));
            jdbcURLString.append(":");
            jdbcURLString.append(props.getProperty("database"));
            conn = DriverManager.getConnection(jdbcURLString.toString(), 
            		props.getProperty("user"), props.getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
    
    public int close() {
		try {
			newStatement.executeBatch();
			conn.commit();
			this.newStatement.close();
			this.statement.close();
			this.conn.close();
			return 1;
		} catch (Exception e) {
			e.printStackTrace(System.err);
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace(System.err);
			}
			return 0;
		}
	}
}